USE [mtl]
GO
/****** Object:  Table [dbo].[T_eCS_NET_USER]    Script Date: 10/28/2014 22:35:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_eCS_NET_USER](
	[PK_NET_ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_T_eCS_NET_USER_PK_NET_ID]  DEFAULT (newid()),
	[C_FULLNAME] [varchar](60) NOT NULL,
	[C_USERNAME] [varchar](50) NOT NULL,
	[C_PASSWORD] [varchar](50) NOT NULL,
	[C_EMAIL] [varchar](200) NOT NULL,
	[C_ID_CARD] [varchar](20) NOT NULL,
	[C_CREATED_DATE] [datetime] NOT NULL,
	[C_XML_DATA] [xml] NULL,
	[C_ROLE] [varchar](50) NULL,
	[C_STATUS] [varchar](50) NULL,
 CONSTRAINT [PK_T_eCS_NET_USER] PRIMARY KEY CLUSTERED 
(
	[PK_NET_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
/*************************************************************************************************************************************

*************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[eCS_NetUserUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[eCS_NetUserUpdate]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[eCS_NetUpdatePass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[eCS_NetUpdatePass]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[eCS_NetCheckLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[eCS_NetCheckLogin]
GO
/************************************************************************************************************************************/
/*
SP: eCS_NetUserUpdateth
Des: cap nhat thong tin dang ky tai khoan
Creater: KHOINV
Date: 11/05/2011
*/
CREATE PROCEDURE dbo.eCS_NetUserUpdate
	@pk_citizen		varchar(50),					--- Khoa
	@p_fullname		varchar(100),					--- Ho va ten cong dan.			
	@p_username		varchar(50),					--- Ten dang nhap.
	@p_password		varchar(50),					---	Mat khau.								
	@p_email		varchar(200),					---	Dia chi hom thu.
	@p_id_card		varchar(20),					--- So chung minh thu.	
	@p_created_date varchar(20),					--- Ngay tao tai khoan.
	@P_xml_data		xml								---xlm
WITH ENCRYPTION
AS
	Declare @count int,@citizen_id int
	SET NOCOUNT ON
	Select @count = count(*) From T_eCS_NET_USER Where C_USERNAME = @p_username And (@pk_citizen = '' or @pk_citizen is null)
	If @count > 0 
	Begin
		Select 'Tên đăng nhập này đã tồn tại, Hãy nhập lại tên đăng nhập! ' RET_ERROR
		Return -100
	End
	SET XACT_ABORT ON
	BEGIN TRANSACTION
		If @pk_citizen != '' and @pk_citizen is not null
		Begin
			Update T_eCS_NET_USER Set
				C_PASSWORD			= @p_password,
				C_EMAIL				= @p_email,
				C_ID_CARD			= @p_id_card,
				C_CREATED_DATE		= @p_created_date
				Where  C_USERNAME	= @p_username
		End
		If @pk_citizen = '' or @pk_citizen is null
		Begin
			Insert Into T_eCS_NET_USER(
						C_FULLNAME,
						C_USERNAME,
					  	C_PASSWORD,
						C_EMAIL,
						C_ID_CARD,
					  	C_CREATED_DATE,
						C_XML_DATA,
						C_STATUS
						)
				 Values(@p_fullname,
					 	@p_username,
						@p_password,
						@p_email,
						@p_id_card,
						getdate(),
						@p_xml_data,
						'HOAT_DONG')
		End
		Select 
			PK_NET_ID
			,C_FULLNAME
			,C_USERNAME
			,C_PASSWORD
			,C_EMAIL
			,C_ID_CARD
			,C_CREATED_DATE
			,C_ROLE
			,C_STATUS
		FROM T_eCS_NET_USER
		WHERE (C_USERNAME = @p_username) and (C_PASSWORD = @p_password)  And C_STATUS = 'HOAT_DONG'

	COMMIT TRANSACTION 
	SET NOCOUNT OFF
Return 0
Go
/*
SP: eCS_NetUpdatePass
Des: thay doi mat khau
Creater: KHOINV
Date: 16/05/2011
*/
CREATE PROCEDURE dbo.eCS_NetUpdatePass

	@userid		varchar(50),
	@p_password_new varchar(50)
WITH ENCRYPTION
AS
	SET NOCOUNT ON	
	Update T_eCS_NET_USER set C_PASSWORD = @p_password_new
	WHERE PK_NET_ID = @userid
	SET NOCOUNT OFF
Return 0
Go
/*
SP: eCS_NetCheckLogin
Des: kiem tra khi dang nhap
Creater: KHOINV
Date: 11/05/2011
*/
CREATE PROCEDURE dbo.eCS_NetCheckLogin
	@p_username varchar(30),
	@p_password varchar(50)
--WITH ENCRYPTION
AS
	SET NOCOUNT ON
	Select 
		PK_NET_ID
		,C_FULLNAME
		,C_USERNAME
		,C_PASSWORD
		,C_EMAIL
		,C_ID_CARD
		,C_CREATED_DATE
		,C_ROLE
		,C_STATUS
	FROM T_eCS_NET_USER
	WHERE (C_USERNAME = @p_username) and (C_PASSWORD = @p_password)  And C_STATUS = 'HOAT_DONG'
	SET NOCOUNT OFF
Return 0



------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[eCS_NetCheckUserName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[eCS_NetCheckUserName]
GO
CREATE PROCEDURE dbo.eCS_NetCheckUserName
	@p_username varchar(30)
--WITH ENCRYPTION
AS
	SET NOCOUNT ON
	Select 
		PK_NET_ID
		,C_FULLNAME
		,C_USERNAME
		,C_PASSWORD
		,C_EMAIL
		,C_ID_CARD
		,C_CREATED_DATE
		,C_ROLE
		,C_STATUS
	FROM T_eCS_NET_USER
	WHERE (C_USERNAME = @p_username) And C_STATUS = 'HOAT_DONG'
	SET NOCOUNT OFF
Return 0
Go
------------------------------
-------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USER_StaffGetAllBySearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USER_StaffGetAllBySearch]
GO
CREATE PROCEDURE dbo.USER_StaffGetAllBySearch
	@p_seach	nvarchar(100)
WITH ENCRYPTION
AS
Declare @v_str_sql nvarchar(500)
	SET NOCOUNT ON
	Set @v_str_sql = ' Select PK_NET_ID,C_FULLNAME, C_USERNAME,C_EMAIL'
	Set @v_str_sql = @v_str_sql + ' ,(select top 1 C_NAME from T_EFYLIB_LIST where C_CODE = T_eCS_NET_USER.C_ROLE) as C_ROLE '
	Set @v_str_sql = @v_str_sql + ' From  T_eCS_NET_USER '
	Set @v_str_sql = @v_str_sql + ' Where 1=1 '
	If @p_seach Is Not Null And @p_seach <>''
		Begin
			Set @v_str_sql = @v_str_sql + ' And  (C_FULLNAME like ' + char(39) + '%' + @p_seach + '%' + char(39)
			Set @v_str_sql = @v_str_sql + ' Or C_EMAIL like ' + char(39) + '%' + @p_seach + '%' + char(39)
			Set @v_str_sql = @v_str_sql + ' Or C_USERNAME like ' + char(39) + '%' + @p_seach + '%' + char(39)
			Set @v_str_sql = @v_str_sql + ' Or C_ID_CARD like ' + char(39) + '%' + @p_seach + '%' + char(39) + ')'
		End
	Set @v_str_sql = @v_str_sql + ' Order By C_ROLE '
	Exec (@v_str_sql)
	SET NOCOUNT OFF
Return 0
Go
-- Exec USER_StaffGetAllBySearch 'ad'
------------------------------
--------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_StaffPermissionUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EfyLib_StaffPermissionUpdate]
GO
CREATE PROCEDURE dbo.EfyLib_StaffPermissionUpdate
	@p_staff_id_list	Nvarchar(max)
	,@p_group			Varchar(50)
AS
	SET XACT_ABORT ON
		update T_eCS_NET_USER set C_ROLE = @p_group where charindex(convert(Varchar(50),PK_NET_ID),@p_staff_id_list) > 0 
	SET NOCOUNT OFF
	Return 0
GO
------------------------------
-----------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].USER_StaffPermissionDelete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].USER_StaffPermissionDelete
GO
CREATE PROCEDURE [dbo].USER_StaffPermissionDelete
	@td01s			Varchar(max)
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		-- Tao bang trung gian
		Declare @tID Table (PK_TRAINING_DOC Varchar(50))
		Insert Into @tID
		Select [part] From [dbo].[f_ConvertStringToTable](@td01s,',')		
		SET XACT_ABORT ON -- Dat che do tu dong Rollback neu co loi xay ra
		BEGIN TRANSACTION
			Delete T_eCS_NET_USER Where PK_NET_ID In (Select PK_TRAINING_DOC From @tID)
		COMMIT TRANSACTION
	SET NOCOUNT OFF
	Return 0
GO